import pymysql

import csv
from datetime import datetime

from conf import mysql_host, mysql_user, mysql_password, mysql_db, mysql_port


def read_csv_and_write_processed():
    processed_rows = []
    with open('processed_conversation.csv', 'r', encoding='utf-8') as csv_data:
        reader = csv.reader(csv_data)

        for row in reader:

            try:
                # 尝试将第三列和第四列的时间数据转换为时间对象
                # 日期部分将被忽略，只保留时间部分

                row[2] = datetime.strptime(row[2], "%H:%M:%S").time()
                row[3] = datetime.strptime(row[3], "%H:%M:%S").time()
            except ValueError:
                row[2] = '00:00:00'
                row[3] = '00:00:00'
                print(row[2], row[3])
            # 将处理后的行添加到列表中
            processed_rows.append(row)

    # 将处理好的数据写入新的CSV文件 'processed_conversation.csv'
    with open('processed_conversation.csv', 'w', newline='', encoding='utf-8') as csv_output:
        writer = csv.writer(csv_output)
        writer.writerows(processed_rows)

    return processed_rows


def convert_csv():
    rows = []
    with open('teacher.csv', 'r', encoding='utf-8') as csv_data:
        reader = csv.reader(csv_data)
        next(reader)  # 跳过标题行
        for row in reader:
            row[2] = True if row[2] == 'Y' else False
            row[6] = True if row[6] == 'Y' else False
            row[1] = 'Female' if row[1] == 'F' else 'Male'
            rows.append(row)
    # 将转换后的数据覆盖写入回 CSV 文件
    with open('teacher.csv', 'w', newline='', encoding='utf-8') as csv_out:
        writer = csv.writer(csv_out)
        writer.writerow(
            ['code', 'gender', 'is_native_speaker', 'collected_recording', 'transcribe_recording', 'audio_quality',
             'needs_special_attention', 'school', 'module', 'degree'])
        writer.writerows(rows)

    return rows


# 数据批量插入数据库
def truncate_text(text, max_length):
    # 如果文本长度超过最大长度，就截断文本；否则，返回原始文本。
    return text[:max_length]


def insert_db(rows):
    db = pymysql.connect(host=mysql_host,
                         port=mysql_port,
                         user=mysql_user,
                         password=mysql_password,
                         database=mysql_db)
    cursor = db.cursor()
    chat_sql = 'insert into t_conversation(chat_id,content,end_time,start_time,talker_name,teacher_name,pause_time) ' \
               'values (%s, %s, %s, %s, %s ,%s, %s) '
    try:
        # 对每一行数据进行处理，确保content字段不超过最大长度
        max_content_length = 255
        processed_rows = [(row[0], truncate_text(row[1], max_content_length), row[2], row[3], row[4], row[5], row[6])
                          for row in rows]

        res = cursor.executemany(chat_sql, processed_rows)
        print(res)
        db.commit()

    finally:
        # 关闭游标和数据库连接
        cursor.close()
        db.close()


convert_csv()
